package com.seeyon.apps.ltc.manager.impl;

import com.seeyon.apps.brwh.kit.DBKit;
import com.seeyon.apps.brwh.kit.DateUtil;
import com.seeyon.apps.brwh.kit.FormCap4Kit;
import com.seeyon.apps.brwh.kit.StrKit;
import com.seeyon.apps.ltc.manager.LtcKanBanSaleStandardDataManager;
import com.seeyon.cap4.form.bean.FormBean;
import com.seeyon.cap4.form.bean.FormTableBean;
import com.seeyon.cap4.form.service.CAP4FormManager;
import com.seeyon.cap4.template.manager.CAPDataListSqlHelpManager;
import com.seeyon.ctp.common.AppContext;
import com.seeyon.ctp.common.exceptions.BusinessException;
import com.seeyon.ctp.common.log.CtpLogFactory;
import com.seeyon.ctp.common.po.ctpenumnew.CtpEnumItem;
import org.apache.commons.logging.Log;
import com.seeyon.cap4.form.bean.FormFieldBean;

import java.math.BigDecimal;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;


/**
 * @author lw
 * @description: 销售规范动作
 * @date 2024年09月11日
 */
public class LtcKanBanSaleStandardDataManagerImpl implements LtcKanBanSaleStandardDataManager {
    private static final Log log = CtpLogFactory.getLog(LtcKanBanSaleStandardDataManagerImpl.class);
    private CAP4FormManager cap4FormManager = (CAP4FormManager) AppContext.getBean("cap4FormManager");

    private CAPDataListSqlHelpManager capDataListSqlHelpManager = (CAPDataListSqlHelpManager) AppContext.getBean("capDataListSqlHelpManager");


    @Override
    public Map<String, Object> getLtcSaleStandardAction() {
        // 返回数据集合
        Map<String, Object> resultDataMap = new HashMap<String, Object>();
        //各项销售规范动作
        List<Object> list = new ArrayList<>();
        //销售管道
        list.add(getSalePipeline());
        //管理线索
        list.add(getManageLeads());
        //管理机会点
        list.add(getManageOpportunity());
        //项目启动
        list.add(getProjectStart());
        //标前引导
        list.add(getBqGuide());
        //提交方案
        list.add(getSubmitProposal());
        //合同签订与谈判
        list.add(getContractNegotiateAndSign());
        //复盘与评议
        list.add(getReviewAndEvaluation());
        //放入返回集
        resultDataMap.put("data", list);
        resultDataMap.put("msg", "销售规范动作");
        return resultDataMap;

    }

    /**
     * 销售管道
     * @return
     */
    public Map<String, Object> getSalePipeline () {
        Map<String, Object> resultDataMap = new HashMap<String, Object>();
        try {
            //项目档案编码
            String xmda_code = AppContext.getSystemProperty("ltc.xmda_code");
            //项目档案-项目金额
            String xmda_xmje = AppContext.getSystemProperty("ltc.xmda_xmje");
            //项目档案-项目状态
            String xmda_xmzt = AppContext.getSystemProperty("ltc.xmda_xmzt");
            //项目档案-当前阶段
            String xmda_dqjd = AppContext.getSystemProperty("ltc.xmda_dqjd");
            //项目状态-进行中
            String xmzt_jxz = AppContext.getSystemProperty("ltc.xmzt_jxz");
            //机会阶段-成交
            String jhjd_cj = AppContext.getSystemProperty("ltc.jhjd_cj");
            //机会阶段-输单
            String jhjd_sd = AppContext.getSystemProperty("ltc.jhjd_sd");
            //机会阶段-赢单
            String jhjd_yd = AppContext.getSystemProperty("ltc.jhjd_yd");

            //机会档案编码
            String jhda_code = AppContext.getSystemProperty("ltc.jhda_code");
            //机会档案-项目金额
            String jhda_xmje = AppContext.getSystemProperty("ltc.jhda_xmje");
            //机会档案-机会点状态
            String jhda_jhdzt = AppContext.getSystemProperty("ltc.jhda_jhdzt");
            //机会点状态-进行中
            String ty_zt_jxz = AppContext.getSystemProperty("ltc.ty_zt_jxz");
            //机会点状态-已完成
            String ty_zt_ywc = AppContext.getSystemProperty("ltc.ty_zt_ywc");
            //机会档案-当前阶段
            String jhda_dqjd = AppContext.getSystemProperty("ltc.jhda_dqjd");
            //机会阶段-机会跟进
            String jhjd_jhgj = AppContext.getSystemProperty("ltc.jhjd_jhgj");


            //线索档案编码
            String xsda_code = AppContext.getSystemProperty("ltc.xsda_code");
            //线索档案-项目金额
            String xsda_xmje = AppContext.getSystemProperty("ltc.xsda_xmje");
            //线索档案-线索状态
            String xsda_main_xszt = AppContext.getSystemProperty("ltc.xsda_main_xszt");
            //线索档案-预签单金额
            String xsda_yqdje = AppContext.getSystemProperty("ltc.xsda_yqdje");
            //线索状态-跟进中
            String xszt_gjz = AppContext.getSystemProperty("ltc.xszt_gjz");

            //销售小组档案编码
            String xsxzda_code = AppContext.getSystemProperty("ltc.xsxzda_code");
            //销售小组档案-订货目标
            String xsxzda_dhmb = AppContext.getSystemProperty("ltc.xsxzda_dhmb");
            //销售小组档案-年度
            String xsxzda_nd = AppContext.getSystemProperty("ltc.xsxzda_nd");
            //销售小组档案-时间-年
            String xsxzda_n=AppContext.getSystemProperty("ltc.xsxzda_n");

            //销售订单档案
            String xsddda_code = AppContext.getSystemProperty("ltc.xsddda_code");
            //销售订单档案-订单金额
            String xsddda_ddje = AppContext.getSystemProperty("ltc.xsddda_ddje");
            //销售订单档案-订单状态
            String xsddda_ddzt = AppContext.getSystemProperty("ltc.xsddda_ddzt");
            //销售订单档案-填报时间
            String xsddda_tbsj = AppContext.getSystemProperty("ltc.xsddda_tb_date");
            //销售订单档案-年度
            String xsddda_nd = AppContext.getSystemProperty("ltc.xsddda_nd");
            //本年年度
            String bnnd = AppContext.getSystemProperty("ltc.bnnd");
            //合同状态-生效
            String htzt_sx = AppContext.getSystemProperty("ltc.htzt_sx");
            //合同状态-完结
            String htzt_wj = AppContext.getSystemProperty("ltc.htzt_wj");


            //在运行项目预订单金额(项目档案-项目状态为进行中的项目金额合计)
            Double xmje = getAmount(xmda_code, xmda_xmzt, Arrays.asList(xmzt_jxz), "xmda_yqdje",bnnd,xmda_dqjd,Arrays.asList(jhjd_yd,jhjd_sd,jhjd_cj));
            //在运行机会点预签单金额（机会档案-机会点状态为进行中的项目金额合计）
            Double jhdje = getAmount(jhda_code, jhda_jhdzt, Arrays.asList(ty_zt_jxz,ty_zt_ywc), "jhda_yqdje" ,bnnd,jhda_dqjd,Arrays.asList(jhjd_jhgj));
            //在运行线索预签单金额（线索档案-线索状态为跟进中的项目金额合计）
            Double xsje = getAmount(xsda_code, xsda_main_xszt, Arrays.asList(xszt_gjz), xsda_yqdje,bnnd,null,null);
            //未验收订单金额（销售订单档案-订单状态为生效和完结的订单金额合计）
            Double wysddje = getAmount("BHMXB_DDWL", "DDZT", Arrays.asList(htzt_sx, htzt_wj), "wysdhjehs",bnnd,null,null);
            //订货目标（销售小组档案-订货目标）
            Double dhmb = getAmount(xsxzda_code, xsxzda_nd, Arrays.asList(StrKit.str(LocalDate.now().getYear())), xsxzda_dhmb,null,null,null);
            //已验收订单金额
            Double ysddje = getAmount("BHMXB_DDWL", "DDZT", Arrays.asList(htzt_sx, htzt_wj), "ysjehs",bnnd,null,null);

            //本年订货目标
            //Double bndhmb = getAmount(xsxzda_code, xsxzda_n, Arrays.asList(StrKit.str(LocalDate.now().getYear())), xsxzda_dhmb,null);
            //本年已经完成订货金额
            //Double bnddje = getAmount("BHMXB_DDWL", "DDZT", Arrays.asList(htzt_sx, htzt_wj), "wysdhjehs",bnnd,null,null);


            log.info("在运行项目预定金额：" + xmje);
            log.info("在运行机会点预签单金额：" + jhdje);
            log.info("在运行线索预签单金额：" + xsje);
            log.info("未验收订单金额：" + wysddje);
            log.info("订货目标：" + dhmb);
            log.info("验收订单金额：" + ysddje);


            //管道支撑率: （未验收订单金额+在运行项目预订单金额/2+在运行机会点预签单金额/6+在运行线索预签单金额/12）/（订货目标-已验收订单金额）
            Double gdratio = (wysddje+xmje/2 + jhdje/6 + xsje/12) / (dhmb - ysddje);
            //线索支撑率：（在运行线索预签单金额/12）/（订货目标-已验收订单金额）
            Double xsratio = xsje/12 / (dhmb - ysddje);
            //机会点支撑率：（在运行机会点预签单金额/6）/（订货目标-已验收订单金额）
            Double jhratio = jhdje/6 / (dhmb - ysddje);
            //项目支撑率：（在运行项目预订单金额/2）/（订货目标-已验收订单金额）
            Double xmratio = xmje/2 / (dhmb - ysddje);
            //未完成订货目标 本年订货目标-本年已经完成订货金额
            Double wwcdjmb=dhmb-ysddje;

            DecimalFormat decimalFormat = new DecimalFormat("#.##");

            resultDataMap.put("gdzcl", decimalFormat.format(gdratio*100));
            resultDataMap.put("xszcl", decimalFormat.format(xsratio*100));
            resultDataMap.put("jhdzcl", decimalFormat.format(jhratio*100));
            resultDataMap.put("xmzcl", decimalFormat.format(xmratio*100));
            resultDataMap.put("dhje", decimalFormat.format(wysddje));
            resultDataMap.put("wwcdjmb", decimalFormat.format(wwcdjmb));
            resultDataMap.put("dhjeUrl", FormCap4Kit.getBbAddres(AppContext.getSystemProperty("ltc.dhje_url")));
            resultDataMap.put("gdzcllUrl", FormCap4Kit.getBbAddres("LTC-管道支撑率"));
            resultDataMap.put("xszclUrl", FormCap4Kit.getBbAddres("LTC-线索支撑率"));
            resultDataMap.put("jhdzclUrl", FormCap4Kit.getBbAddres("LTC-机会点支撑率"));
            resultDataMap.put("xmzclUrl", FormCap4Kit.getBbAddres("LTC-项目支撑率"));
            resultDataMap.put("wwcdhmbUrl", FormCap4Kit.getBbAddres("LTC-未完成订货目标"));

            //将数据放入结果数据Map
            resultDataMap.put("code", 200);
            resultDataMap.put("title", "销售管道");
            resultDataMap.put("msg", "获取销售管道数据成功");
            return resultDataMap;

        } catch (Exception e) {
            /* 记录解析异常日志，并返回空的结果数据Map */
            log.error("获取销售管道数据异常:"+ e.getMessage());
            resultDataMap.put("code", 500);
            resultDataMap.put("title", "销售管道");
            resultDataMap.put("msg", e.getMessage());
            return resultDataMap;
        }
    }

    /**
     * 管理线索
     *
     * @return
     */
    public Map<String, Object> getManageLeads() {
        Map<String, Object> resultDataMap = new HashMap<String, Object>();
        try {
            //线索档案编码
            String xsda_code = AppContext.getSystemProperty("ltc.xsda_code");
            //线索状态-主表
            String xsda_main_xszt = AppContext.getSystemProperty("ltc.xsda_main_xszt");
            //线索状态-转机会
            String xszt_zjh = AppContext.getSystemProperty("ltc.xszt_zjh");
            CtpEnumItem zjhEnum = FormCap4Kit.getEnumByCode(xszt_zjh);
            //线索状态-跟进中
            String xszt_gjz = AppContext.getSystemProperty("ltc.xszt_gjz");
            CtpEnumItem gjzEnum = FormCap4Kit.getEnumByCode(xszt_gjz);
            //根据表单编号获取formBean对象
            FormBean xsda_formBean = cap4FormManager.getFormByFormCode(xsda_code);
            //获取表单字段
            List<FormFieldBean> xsda_formFieldBeans = xsda_formBean.getMasterTableBean().getFields();
            //获取机会点登记编码
            String jhdj001 = AppContext.getSystemProperty("ltc.jhdj001");
            //本年年度
            String bnnd = AppContext.getSystemProperty("ltc.bnnd");
            //获取表名
            FormBean jhdj001_formBean = cap4FormManager.getFormByFormCode(jhdj001);
            String jhdjTableName = jhdj001_formBean.getMasterTableBean().getTableName();
            //线索名称
            String jhmcFieldName=jhdj001_formBean.getFieldBeanByDisplay("线索名称").getName();



            // 初始化用于构建SQL查询的变量
            String tableName = "";
            String xsztFieldName = "";
            String bnndFieldName = "";

            for (FormFieldBean formFieldBean : xsda_formFieldBeans) {
                String fieldDesc = StrKit.str(formFieldBean.getDesc().trim());
                if (xsda_main_xszt.equals(fieldDesc)) {
                    xsztFieldName = formFieldBean.getName();
                    tableName = formFieldBean.getOwnerTableName();
                }
                if (bnnd.equals(fieldDesc)) {
                    bnndFieldName = formFieldBean.getName();
                }
            }
            /*
             * SELECT CASE WHEN total_count = 0 THEN 0.0000 ELSE ROUND((xszt_zjh_count * 1.0 / total_count), 4) END AS xszt FROM(SELECT COUNT(*) AS total_count,(SELECT COUNT(*) FROM user WHERE xszt = 1) AS xszt_zjh_count FROM xsda) AS subquery;
             * */
            // 根据已确定的条件构建SQL查询语句
            StringBuilder selectSql = new StringBuilder();
            //查询线索转化率（已转化机会点线索数量（线索状态转机会）/总线索数量）
            if (!"".equals(xsztFieldName) && !"".equals(tableName) && !"".equals(bnndFieldName)) {
                selectSql.append("SELECT CASE WHEN total_count = 0 THEN 0.00 ELSE ROUND((xszt_zjh_count * 1.0 / total_count*100), 2) END AS xszhl");
                selectSql.append(" FROM (SELECT COUNT(*) AS total_count, (SELECT COUNT(*) FROM ").append(tableName).append(" WHERE ");
                selectSql.append(xsztFieldName).append(" = '").append(zjhEnum.getId()).append("' and ").append(bnndFieldName).append(" = '").append(LocalDate.now().getYear())
                        .append("' ) AS xszt_zjh_count FROM ").append(tableName)
                        .append(" where ").append(bnndFieldName).append(" = '").append(LocalDate.now().getYear()).append("') AS subquery;");
            }
            //存储线索转化率
            String xszhl = "";

            // 日志记录构建的SQL查询语句
            log.info("查询线索转化率sql:" + selectSql.toString());

            //从数据库获取数据线索转化率
            List<Map<String, Object>> xszhlList = DBKit.getDataBae(selectSql.toString());
            if (!xszhlList.isEmpty()) {
                xszhl = StrKit.str(xszhlList.get(0).get("xszhl"));
            }

            //查询上周关闭线索数
            //获取当前时间
            LocalDate now = LocalDate.now();
            //获取上个自然周开始时间
            String endTime = DateUtil.formatDateTime(DateUtil.getPreviousWeekEnd(now));
            //获取上个自然周结束时间
            String startTime = DateUtil.formatDateTime(DateUtil.getPreviousWeekStart(now));
            //组装sql查询关闭线索数
            StringBuilder selectSzgbxssSql = new StringBuilder();
            if (!"".equals(jhdjTableName)) {
                selectSzgbxssSql.append("select count(*) as szgbxss from ");
                selectSzgbxssSql.append(jhdjTableName).append(" f left join col_summary cs on f.id=cs.FORM_RECORDID where cs.state=3 ");
                selectSzgbxssSql.append("and f.").append(jhmcFieldName).append(" is not null ");
                selectSzgbxssSql.append("and (FINISH_DATE between '").append(startTime).append("' and '").append(endTime).append("')");
            }
            // 日志记录构建的SQL查询语句
            log.info("查询上周关闭线索数sql:" + selectSzgbxssSql.toString());
            //存储上周关闭线索数
            String szgbxss = "";
            //从数据库获取上周关闭线索数
            List<Map<String, Object>> szgbxssList = DBKit.getDataBae(selectSzgbxssSql.toString());
            if (!szgbxssList.isEmpty()) {
                szgbxss = StrKit.str(szgbxssList.get(0).get("szgbxss"));
            }

            //将数据放入结果数据Map
            //管道支撑率
            resultDataMap.put("gdzcl", getSalePipeline().get("gdzcl"));
            resultDataMap.put("xszhl", xszhl);
            resultDataMap.put("xszhlUrl", FormCap4Kit.getBbAddres(AppContext.getSystemProperty("ltc.xszhl_url")));
            resultDataMap.put("szgbxss", szgbxss);
            resultDataMap.put("szgbxssUrl", FormCap4Kit.getBbAddres(AppContext.getSystemProperty("ltc.szgbxss_url")));
            resultDataMap.put("code", 200);
            resultDataMap.put("title", "管理线索");
            resultDataMap.put("msg", "获取管理线索数据成功");
            resultDataMap.put("jhdsxjsllUrl", FormCap4Kit.getBbAddres("LTC-机会点刷新及时率"));
            return resultDataMap;


        } catch (Exception e) {
            /* 记录解析异常日志，并返回空的结果数据Map */
            log.error("获取线索管理数据异常:"+e.getMessage());
            resultDataMap.put("code", 500);
            resultDataMap.put("title", "管理线索");
            resultDataMap.put("msg", e.getMessage());
            return resultDataMap;
        }
    }

        /**
         * 管理机会点
         * @return
         */
        public Map<String, Object> getManageOpportunity () {
            Map<String, Object> resultDataMap = new HashMap<String, Object>();
            try {
                //机会档案编码
                String jhda_code = AppContext.getSystemProperty("ltc.jhda_code");
                //机会点状态
                String jhda_jhdzt = AppContext.getSystemProperty("ltc.jhda_jhdzt");
                //跟进日期
                String jhda_gjrq = AppContext.getSystemProperty("ltc.jhda_gjrq");
                //机会点状态-进行中
                String ty_zt_jxz = AppContext.getSystemProperty("ltc.ty_zt_jxz");
                //当前阶段
                String jhda_dqjd = AppContext.getSystemProperty("ltc.jhda_dqjd");
                //机会阶段-机会跟进
                String jhjd_jhgj = AppContext.getSystemProperty("ltc.jhjd_jhgj");
                //本年年度
                String bnnd = AppContext.getSystemProperty("ltc.bnnd");

                //获取机会档案的表单字段
                FormBean jhda_formBean = cap4FormManager.getFormByFormCode(jhda_code);
                List<FormFieldBean> jhda_formFieldBeans = jhda_formBean.getAllFieldBeans();
                //获取枚举对象
                CtpEnumItem jxzEnum = FormCap4Kit.getEnumByCode(ty_zt_jxz);
                CtpEnumItem jhgjEnum = FormCap4Kit.getEnumByCode(jhjd_jhgj);
                //上周一
                LocalDate start = DateUtil.getPreviousWeekStart(LocalDate.now()).toLocalDate();
                LocalDate now = LocalDate.now();


                // 初始化用于构建SQL查询的变量
                String jhda_tableName = "";
                String gjrq_fieldName = "";
                String jhdzt_fieldName = "";
                String jhda_dqjd_fieldName = "";
                String bnnd_fieldName = "";

                /*（机会点状态=进行中）按周刷新的机会点÷（机会点状态=进行中）机会点总数*100%*/
                for (FormFieldBean formFieldBean : jhda_formFieldBeans) {
                    String fieldDesc = StrKit.str(formFieldBean.getDesc().trim());
                    if (jhda_jhdzt.equals(fieldDesc)) {
                        jhdzt_fieldName = formFieldBean.getName();
                        jhda_tableName = formFieldBean.getOwnerTableName();
                    }
                    if (jhda_dqjd.equals(fieldDesc)) {
                        jhda_dqjd_fieldName = formFieldBean.getName();
                    }

                    if (jhda_gjrq.equals(fieldDesc)) {
                        gjrq_fieldName = formFieldBean.getName();
                    }

                    if (bnnd.equals(fieldDesc)) {
                        bnnd_fieldName = formFieldBean.getName();
                    }

                }
                /*按周刷新（跟进日期晚于上周一）的机会点/机会点总数 都取机会点状态为进行中的*/
                // 根据已确定的条件构建SQL查询语句
                StringBuilder selectSql = new StringBuilder();
                StringBuilder selectSql2 = new StringBuilder();
                StringBuilder selectSql3 = new StringBuilder();

                //机会点刷新及时率
                if (!"".equals(jhda_tableName) && !"".equals(jhdzt_fieldName) && !"".equals(gjrq_fieldName) && !"".equals(jhda_dqjd_fieldName) && !"".equals(bnnd_fieldName) ) {

                    selectSql.append("SELECT ");
                    selectSql.append("CASE WHEN t1.fm = 0 THEN 0.00 ELSE CAST(ROUND((t2.fz * 1.0 / t1.fm * 100), 2) AS DECIMAL(10, 2)) END AS ratio ");
                    selectSql.append("FROM ");
                    selectSql.append("(SELECT COUNT(*) AS fm FROM ").append(jhda_tableName).append(" WHERE ").append(jhdzt_fieldName).append("='").append(jxzEnum.getId()).append("' and ").append(jhda_dqjd_fieldName).append("='").append(jhgjEnum.getId()).append("' and ").append(bnnd_fieldName).append(" = '").append(LocalDate.now().getYear()).append("') t1,");
                    selectSql.append("(SELECT COUNT(*) AS fz FROM ").append(jhda_tableName).append(" WHERE ").append(jhdzt_fieldName).append("='").append(jxzEnum.getId()).append("' AND ").append(jhda_dqjd_fieldName).append("='").append(jhgjEnum.getId()).append("' and DATEDIFF(day, ").append(gjrq_fieldName).append(", GETDATE()) <= 7 and ").append(bnnd_fieldName).append(" = '").append(LocalDate.now().getYear()).append("') t2");

                    selectSql2.append("SELECT COUNT(*) AS fm FROM ").append(jhda_tableName).append(" WHERE ").append(jhdzt_fieldName).append("='").append(jxzEnum.getId()).append("' and ").append(jhda_dqjd_fieldName).append("='").append(jhgjEnum.getId()).append("' and ").append(bnnd_fieldName).append(" = '").append(LocalDate.now().getYear()).append("'");
                    selectSql3.append("SELECT COUNT(*) AS fz FROM ").append(jhda_tableName).append(" WHERE ").append(jhdzt_fieldName).append("='").append(jxzEnum.getId()).append("' AND ").append(jhda_dqjd_fieldName).append("='").append(jhgjEnum.getId()).append("' and DATEDIFF(day, ").append(gjrq_fieldName).append(", GETDATE()) <= 7 and ").append(bnnd_fieldName).append(" = '").append(LocalDate.now().getYear()).append("'");

                }else {
                    throw new Exception("查询机会点刷新及时率失败，表单字段信息有误！");
                }
                //存储机会点刷新及时率
                String ratio = "";
                // 日志记录构建的SQL查询语句
                log.info("机会点刷新及时率sql:" + selectSql.toString());
                log.info("机会点刷新及时率sql:" + selectSql2.toString());
                log.info("机会点刷新及时率sql2:" + selectSql3.toString());


                //从数据库获取机会点刷新及时率
               List<Map<String, Object>> resultList = DBKit.getDataBae(selectSql.toString());
               List<Map<String, Object>> resultList2 = DBKit.getDataBae(selectSql2.toString());
               List<Map<String, Object>> resultList3 = DBKit.getDataBae(selectSql3.toString());

                if (!resultList.isEmpty()) {
                    ratio = StrKit.str(resultList.get(0).get("ratio"));
                }
                if (!resultList2.isEmpty()) {
                    log.info("机会点刷新及时率的分母:" + StrKit.str(resultList2.get(0).get("fm")));
                }
                if (!resultList3.isEmpty()) {
                    log.info("机会点刷新及时率的分子:" + StrKit.str(resultList3.get(0).get("fz")));
                }

                //将数据放入结果数据Map
                resultDataMap.put("jhdsxjsl", ratio);
                resultDataMap.put("jhdzcl", getSalePipeline().get("jhdzcl"));
                resultDataMap.put("code", 200);
                resultDataMap.put("title", "管理机会点");
                resultDataMap.put("msg", "获取管理机会点数据成功");
                return resultDataMap;

            } catch (Exception e) {
                /* 记录解析异常日志，并返回空的结果数据Map */
                log.error("获取管理机会点数据异常:"+ e.getMessage());
                resultDataMap.put("code", 500);
                resultDataMap.put("title", "管理机会点");
                resultDataMap.put("msg", e.getMessage());
                return resultDataMap;
            }
        }
        /*查询处理项目启动模块*/
        private List<Map<String, Object>> queryProjectStartSql(String fileName,String tableName,String sf,String fielldName1,String fielldName2,String nd) throws Exception {
            List<Map<String, Object>> list=new ArrayList<Map<String, Object>>();
            try {
                StringBuilder querySql = new StringBuilder();
                querySql.append("SELECT COUNT(*) AS count FROM ").append(tableName);
                if (!fileName.equals("")){
                    querySql.append(" ").append("WHERE").append(" ").append(fileName).append(" = ").append(sf).append(" AND ").append(fileName).append(" IS NOT NULL and ")
                            .append(fielldName2).append(" IS NOT NULL and ").append(fielldName1).append(" is not null and ").append(nd).append(" = '").append(LocalDate.now().getYear()).append("'");
                }
                log.info("queryProjectStartSql查询语句"+querySql.toString());
                list= capDataListSqlHelpManager.selectDataList(querySql.toString(), null);
            }catch (Exception e){
                log.info("方法queryProjectStartSql报错"+e.getMessage());
            }
            return list;
        }

    private List<Map<String, Object>> queryProjectStartAllSql(String fileName,String tableName,String fieldName1,String fieldName2,String nd) throws Exception {
        List<Map<String, Object>> list=new ArrayList<Map<String, Object>>();
        try {
            StringBuilder querySql = new StringBuilder();
            querySql.append("SELECT COUNT(*) AS count FROM ").append(tableName);
            if (!fileName.equals("")){
                querySql.append(" ").append("WHERE ").append(fileName).append(" is not null and ").append(fieldName1).append(" is not null and ").append(fieldName2).append(" IS NOT NULL and ").append(nd).append(" = '").append(LocalDate.now().getYear()).append("'");
            }
            log.info("queryProjectStartSql查询语句"+querySql.toString());
            list= capDataListSqlHelpManager.selectDataList(querySql.toString(), null);
        }catch (Exception e){
            log.info("方法queryProjectStartSql报错"+e.getMessage());
        }
        return list;
    }


        /**
         * 项目启动
         * @return
         */
        public Map<String, Object> getProjectStart () {
            Map<String, Object> resultDataMap = new HashMap<String, Object>();
            try {
                //立项审批完成时间
                String xmda_lxspwcsj = AppContext.getSystemProperty("ltc.xmda_lxspwcsj");
                //客户发标时间
                String xmda_khfbsj = AppContext.getSystemProperty("ltc.xmda_khfbsj");
                //创建策划报告时间
                String xmda_cjchbgsj = AppContext.getSystemProperty("ltc.xmda_cjchbgsj");
                //主计划制定时间
                String xmda_zjhzdsj = AppContext.getSystemProperty("ltc.xmda_zjhzdsj");
                /*项目管理-项目策划报告*/
                FormBean xmchFormBean = cap4FormManager.getFormByFormCode("XMCH_001");
                if (xmchFormBean!=null){
                    String ch="";
                    String zjh="";
                    String kgh="";
                    String chSum="";
                    String zjhSum="";
                    String kghSum="";
                    String sf="";
                    String tableName="";
                    String nd=xmchFormBean.getFieldBeanByDisplay("年度值").getName();
                    String isJsch=xmchFormBean.getFieldBeanByDisplay("策划报告是否及时").getName();
                    String chbgcgsj=xmchFormBean.getFieldBeanByDisplay("策划报告草稿时间").getName();
                    String dacjsj=xmchFormBean.getFieldBeanByDisplay("档案创建时间").getName();

                    String isJszjh=xmchFormBean.getFieldBeanByDisplay("主计划制定是否及时").getName();
                    String zjhbzwcsj=xmchFormBean.getFieldBeanByDisplay("主计划编制完成时间").getName();
                    String kghhsj=xmchFormBean.getFieldBeanByDisplay("开工会后时间").getName();

                    //档案创建时间、开工会后时间
                    String isJskgh=xmchFormBean.getFieldBeanByDisplay("开工会是否及时").getName();


                    tableName=xmchFormBean.getFieldBeanByDisplay("开工会是否及时").getOwnerTableName();
                    CtpEnumItem enumItem = FormCap4Kit.getEnumByCode("Y");
                    if (enumItem!=null){
                        sf=StrKit.str(enumItem.getId());
                    }
                    /*查询策划报告所有条数*/
                    List<Map<String, Object>> list = queryProjectStartAllSql(isJsch, tableName,chbgcgsj,dacjsj,nd);
                    if(!list.isEmpty()){
                        Map<String, Object> stringObjectMap =( Map<String, Object>)list.get(0);
                        ch=StrKit.str(stringObjectMap.get("count"));
                    }
                    /*主计划制定及时率所有条数*/
                    List<Map<String, Object>> zjharr = queryProjectStartAllSql(isJszjh, tableName,zjhbzwcsj,kghhsj,nd);
                    if(!zjharr.isEmpty()){
                        Map<String, Object> stringObjectMap =( Map<String, Object>)zjharr.get(0);
                        zjh=StrKit.str(stringObjectMap.get("count"));
                    }
                    /*开工会及时率所有条数*/
                    List<Map<String, Object>> kghlist = queryProjectStartAllSql(isJskgh, tableName,dacjsj,kghhsj,nd);
                    if(!kghlist.isEmpty()){
                        Map<String, Object> stringObjectMap =( Map<String, Object>)kghlist.get(0);
                        kgh=StrKit.str(stringObjectMap.get("count"));
                    }
                    /*查询策划报告及时率*/
                    List<Map<String, Object>> chlist = queryProjectStartSql(isJsch, tableName, sf,chbgcgsj,dacjsj,nd);
                    if(!chlist.isEmpty()){
                        Map<String, Object> chMap =( Map<String, Object>)chlist.get(0);
                        chSum=StrKit.str(chMap.get("count"));
                    }
                    /*主计划制定及时率*/
                    List<Map<String, Object>> zjhlist = queryProjectStartSql(isJszjh, tableName, sf,zjhbzwcsj,kghhsj,nd);
                    if(!zjhlist.isEmpty()){
                        Map<String, Object> zjhMap =( Map<String, Object>)zjhlist.get(0);
                        zjhSum=StrKit.str(zjhMap.get("count"));
                    }
                    /*开工会及时率*/
                    List<Map<String, Object>> kghList = queryProjectStartSql(isJskgh, tableName, sf,dacjsj,kghhsj,nd);
                    if(!isJskgh.isEmpty()){
                        Map<String, Object> kghMap =( Map<String, Object>)kghList.get(0);
                        kghSum=StrKit.str(kghMap.get("count"));
                    }
                    if(ch.equals("0")){
                        resultDataMap.put("chbgjsl", "0");
                    }else {
                        resultDataMap.put("chbgjsl", Double.parseDouble(chSum)/Double.parseDouble(ch)*100);
                    }

                    if(zjh.equals("0")){
                        resultDataMap.put("zjhzdjsl", "0");
                    }else {
                        resultDataMap.put("zjhzdjsl", Double.parseDouble(zjhSum)/Double.parseDouble(zjh)*100);
                    }

                    if(kgh.equals("0")){
                        resultDataMap.put("kghjsl", "0");
                    }else {
                        resultDataMap.put("kghjsl", Double.parseDouble(kghSum)/Double.parseDouble(kgh)*100);
                    }
                }else {
                   throw new Exception("策划项目档案未找到");
                }


                //立项及时率:(客户发标时间-立项审批完成时间<=30*24小时)/项目档案总数
                Map<String, Object> lxjsl = getXmjsRate(xmda_khfbsj, xmda_lxspwcsj, 30*24);
                if (lxjsl != null && 200==(int)lxjsl.get("code")) {
                    resultDataMap.put("lxjsl", lxjsl.get("ratio"));
                }else {
                    throw new Exception(StrKit.str(lxjsl.get("msg")));
                }

                ////策划报告及时率:(创建策划报告时间-立项审批完成时间<=24小时)/项目档案总数
                //Map<String, Object> chbjsl = getXmjsRate(xmda_cjchbgsj, xmda_lxspwcsj, 24);
                //if (chbjsl != null && 200==(int)chbjsl.get("code")) {
                //    resultDataMap.put("chbgjsl", chbjsl.get("ratio"));
                //}else {
                //    throw new Exception(StrKit.str(chbjsl.get("msg")));
                //}
                //
                ////主计划制定及时率:（主计划制定时间-立项审批完成时间<=24小时)/项目档案总数
                //Map<String, Object> zjhzdsjsl = getXmjsRate(xmda_zjhzdsj, xmda_lxspwcsj, 24);
                //if (zjhzdsjsl != null && 200==(int)zjhzdsjsl.get("code")) {
                //    resultDataMap.put("zjhzdjsl", zjhzdsjsl.get("ratio"));
                //}else {
                //    throw new Exception(StrKit.str(zjhzdsjsl.get("msg")));
                //}
                //
                ////开工会及时率:(创建策划报告时间-立项审批完成时间<=24小时)/项目档案总数
                //Map<String, Object> kghsjsl = getXmjsRate(xmda_cjchbgsj, xmda_lxspwcsj, 24);
                //if (kghsjsl != null && 200==(int)kghsjsl.get("code")) {
                //    resultDataMap.put("kghjsl", kghsjsl.get("ratio"));
                //}else {
                //    throw new Exception(StrKit.str(kghsjsl.get("msg")));w
                //}


                resultDataMap.put("lxjsllUrl", FormCap4Kit.getBbAddres("LTC-立项及时率"));
                resultDataMap.put("chbgjsllUrl", FormCap4Kit.getBbAddres("LTC-策划报告及时率"));
                resultDataMap.put("zjhzdjsllUrl", FormCap4Kit.getBbAddres("LTC-主计划制定及时率"));
                resultDataMap.put("kghjsllUrl", FormCap4Kit.getBbAddres("LTC-开工会及时率"));
                resultDataMap.put("code", 200);
                resultDataMap.put("msg", "获取项目启动数据成功");
                resultDataMap.put("title", "项目启动");
                return resultDataMap;



            } catch (Exception e) {
                /* 记录解析异常日志，并返回空的结果数据Map */
                log.error("获取项目启动数据异常:"+e.getMessage());
                resultDataMap.put("code", 500);
                resultDataMap.put("msg", e.getMessage());
                resultDataMap.put("title", "项目启动");
                return resultDataMap;
            }
        }

        /**
         * 标前引导
         * @return
         */
        public Map<String, Object> getBqGuide () {
            Map<String, Object> resultDataMap = new HashMap<String, Object>();
            try {
                //ltc里程碑阶段-标前引导策略制定（标前策略分析及时率）
                String ltc_bqydclzd = AppContext.getSystemProperty("ltc.ltc_bqydclzd");
                //ltc里程碑阶段-客户确认标前引导方案（标前方案确认及时率）
                String ltc_khqrbqydfa = AppContext.getSystemProperty("ltc.ltc_khqrbqydfa");

                //获取标前策略分析及时率
                Map<String, Object> ltc_bqydclzd_jsRate = getJsRate(ltc_bqydclzd);
                //获取标前方案确认及时率
                Map<String, Object> ltc_khqrbqydfa_jsRate = getJsRate(ltc_khqrbqydfa);
                //将数据放入结果数据Map
                if (ltc_bqydclzd_jsRate != null && 200==(int)ltc_bqydclzd_jsRate.get("code")) {
                    resultDataMap.put("bqcl", ltc_bqydclzd_jsRate.get("ratio"));
                }else {
                    throw new Exception(StrKit.str(ltc_bqydclzd_jsRate.get("msg")));
                }
                if (ltc_khqrbqydfa_jsRate != null && 200==(int)ltc_khqrbqydfa_jsRate.get("code")) {
                    resultDataMap.put("bqfa", ltc_khqrbqydfa_jsRate.get("ratio"));
                }else {
                    throw new Exception(StrKit.str(ltc_khqrbqydfa_jsRate.get("msg")));
                }
                resultDataMap.put("bqclfxjsllUrl", FormCap4Kit.getBbAddres("LTC-标前策略分析及时率"));
                resultDataMap.put("bqfaqrjsllUrl", FormCap4Kit.getBbAddres("LTC-标前方案确认及时率"));
                resultDataMap.put("code", 200);
                resultDataMap.put("msg", "获取标前引导数据成功");
                resultDataMap.put("title", "标前引导");

                return resultDataMap;


            } catch (Exception e) {
                /* 记录解析异常日志，并返回空的结果数据Map */
                log.error("获取标前引导数据异常:", e);
                resultDataMap.put("code", 500);
                resultDataMap.put("msg", e.getMessage());
                resultDataMap.put("title", "标前引导");
                return resultDataMap;
            }
        }

        /**
         * 提交方案
         * @return
         */
        public Map<String, Object> getSubmitProposal () {
            Map<String, Object> resultDataMap = new HashMap<String, Object>();
            try {
                //ltc里程碑阶段-解决方案策略制定（策略及时率）
                String ltc_jjfaclzd = AppContext.getSystemProperty("ltc.ltc_jjfaclzd");
                //ltc里程碑阶段-ATB评审（ATB及时率）
                String ltc_atbps = AppContext.getSystemProperty("ltc.ltc_atbps");

                //获取策略及时率
                Map<String, Object> ltc_jjfaclzd_jsRate = getJsRate(ltc_jjfaclzd);
                //获取ATB及时率
                Map<String, Object> ltc_atbps_jsRate = getJsRate(ltc_atbps);
                //将数据放入结果数据Map
                if (ltc_jjfaclzd_jsRate != null && 200==(int)ltc_jjfaclzd_jsRate.get("code")) {
                    resultDataMap.put("cl", ltc_jjfaclzd_jsRate.get("ratio"));
                }else {
                    throw new Exception(StrKit.str(ltc_jjfaclzd_jsRate.get("msg")));
                }
                if (ltc_atbps_jsRate != null && 200==(int)ltc_atbps_jsRate.get("code")) {
                    resultDataMap.put("atb", ltc_atbps_jsRate.get("ratio"));
                }else {
                    throw new Exception(StrKit.str(ltc_atbps_jsRate.get("msg")));
                }
                resultDataMap.put("atbjsllUrl", FormCap4Kit.getBbAddres("LTC-ATB及时率"));
                resultDataMap.put("cljsllUrl", FormCap4Kit.getBbAddres("LTC-策略及时率"));
                resultDataMap.put("code", 200);
                resultDataMap.put("msg", "获取提交方案数据成功");
                resultDataMap.put("title", "提交方案");

                return resultDataMap;


            } catch (Exception e) {
                /* 记录解析异常日志，并返回空的结果数据Map */
                log.error("获取提交方案数据异常:", e);
                resultDataMap.put("code", 500);
                resultDataMap.put("msg", e.getMessage());
                resultDataMap.put("title", "提交方案");
                return resultDataMap;
            }
        }

        /**
         * 合同谈判与签订
         * @return
         */
        public Map<String, Object> getContractNegotiateAndSign () {
            Map<String, Object> resultDataMap = new HashMap<String, Object>();
            try {
                //ltc里程碑阶段-合同谈判策略制定（谈判策略及时率）
                String ltc_httpclzd = AppContext.getSystemProperty("ltc.ltc_httpclzd");
                //获取策略及时率
                Map<String, Object> ltc_httpclzd_jsRate = getJsRate(ltc_httpclzd);
                //将谈判策略及时率数据放入结果数据Map
                if (ltc_httpclzd_jsRate != null && 200==(int)ltc_httpclzd_jsRate.get("code")) {
                    resultDataMap.put("tpcl", ltc_httpclzd_jsRate.get("ratio"));
                }else {
                    throw new Exception(StrKit.str(ltc_httpclzd_jsRate.get("msg")));
                }
                //合同决策意见闭环率
                Map<String, Object> htjcyjbhl = getHtjcyjbhl();
                if (htjcyjbhl != null && 200==(int)htjcyjbhl.get("code")) {
                    resultDataMap.put("htjc", htjcyjbhl.get("ratio"));
                }else {
                    throw new Exception(StrKit.str(htjcyjbhl.get("msg")));
                }

                resultDataMap.put("htjcyjbhllUrl", FormCap4Kit.getBbAddres("LTC-合同决策意见闭环率"));
                resultDataMap.put("httpcljslUrl", FormCap4Kit.getBbAddres("LTC-合同谈判策略及时率"));
                resultDataMap.put("code", 200);
                resultDataMap.put("msg", "获取合同谈判与签订数据成功");
                resultDataMap.put("title", "合同谈判与签订");

                return resultDataMap;


            } catch (Exception e) {
                /* 记录解析异常日志，并返回空的结果数据Map */
                log.error("获取合同谈判与签订数据异常:", e);
                resultDataMap.put("code", 500);
                resultDataMap.put("msg", e.getMessage());
                resultDataMap.put("title", "合同谈判与签订");
                return resultDataMap;
            }
        }

        /**
         * 复盘与评议
         * @return
         */
        public Map<String, Object> getReviewAndEvaluation () {
            Map<String, Object> resultDataMap = new HashMap<String, Object>();
            try {
                //LTC-项目WBS档案编码
                String yfxmda_formCode = AppContext.getSystemProperty("ltc.yfxmda_formCode");

                //获取项目档案的表单字段
                FormBean yfxmda_formBean = cap4FormManager.getFormByFormCode(yfxmda_formCode);
                //LTC计划结束时间
                String ltc_jhjssj = yfxmda_formBean.getFieldBeanByDisplay("LTC计划结束时间").getName();
                String ltc_sjwcsj = yfxmda_formBean.getFieldBeanByDisplay("LTC主计划实际完成时间").getName();
                String ltc_zjhwcs = yfxmda_formBean.getFieldBeanByDisplay("LTC子计划完成数").getName();
                String ltc_nd = yfxmda_formBean.getFieldBeanByDisplay("年度值").getName();
                String ltc_lcb = yfxmda_formBean.getFieldBeanByDisplay("LTC里程碑").getName();
                String ltc_sjjhzt = yfxmda_formBean.getFieldBeanByDisplay("实际计划状态").getName();
                String yfxmda_ltczjh = yfxmda_formBean.getFieldBeanByDisplay("LTC计划结束时间").getOwnerTableName();

                //IPD-计划状态 取消、关闭枚举
                CtpEnumItem lcbzt_qxgb = FormCap4Kit.getEnumByCode("LCBZT_QXGB");
                //LTC-LTC里程碑  复盘
                CtpEnumItem ltc_lcbjdfp = FormCap4Kit.getEnumByCode("ltc_lcbjdfp");


                /*复盘及时率 = （ltc计划结束时间-ltc实际完成时间>=0 and LTC子计划完成数<>null and LTC里程碑=复盘 and 年度=2024 and 实际计划状态<>取消关闭）数量 / (年度=2024 and 实际计划状态<>取消关闭)数量*/
                // 根据已确定的条件构建SQL查询语句
                StringBuilder selectSql = new StringBuilder();
                //查询复盘及时率
                if (!"".equals(yfxmda_ltczjh) && !"".equals(ltc_jhjssj) &&  !"".equals(ltc_sjwcsj) && !"".equals(ltc_lcb) && !"".equals(ltc_zjhwcs) && !"".equals(ltc_nd) && !"".equals(ltc_sjjhzt) ) {

                    // 主查询
                    selectSql.append("SELECT CASE WHEN total_count = 0 THEN 0.00 ELSE ROUND(( early_count * 1.0 / total_count * 100), 2) END AS ratio ");

                    // 子查询
                    selectSql.append(" FROM (SELECT ");
                    selectSql.append(" COUNT(CASE WHEN ");
                    selectSql.append(ltc_jhjssj).append(" >= ").append(ltc_sjwcsj);
                    selectSql.append(" AND ").append(ltc_lcb).append(" = '").append(ltc_lcbjdfp.getId()).append("' ");
                    selectSql.append(" AND ").append(ltc_nd).append(" = '").append(LocalDate.now().getYear()).append("' ");
                    selectSql.append(" AND ").append(ltc_sjjhzt).append(" <> '").append(lcbzt_qxgb.getId()).append("' ");
                    selectSql.append(" THEN 1 END) AS early_count, ");
                    selectSql.append(" COUNT(CASE WHEN ");
                    selectSql.append(ltc_jhjssj).append(" IS NOT NULL ");
                    selectSql.append(" AND ").append(ltc_sjwcsj).append(" IS NOT NULL ");
                    selectSql.append(" AND ").append(ltc_lcb).append(" = '").append(ltc_lcbjdfp.getId()).append("' ");
                    selectSql.append(" AND ").append(ltc_nd).append(" = '").append(LocalDate.now().getYear()).append("' ");
                    selectSql.append(" AND ").append(ltc_sjjhzt).append(" <> '").append(lcbzt_qxgb.getId()).append("' ");
                    selectSql.append(" THEN 1 END) AS total_count ");
                    selectSql.append(" FROM ").append(yfxmda_ltczjh).append(") AS subquery");
                }else {
                    throw new Exception("查询复盘及时率失败，表单字段信息有误！");
                }
                //存储复盘及时率
                String ratio = "";

                // 日志记录构建的SQL查询语句
                log.info("查询复盘及时率sql:" + selectSql.toString());

                //从数据库获取闭环率
                List<Map<String, Object>> resultList = DBKit.getDataBae(selectSql.toString());
                if (!resultList.isEmpty()) {
                    ratio = StrKit.str(resultList.get(0).get("ratio"));
                }

                //将数据放入结果数据Map
                resultDataMap.put("fpjslUrl", FormCap4Kit.getBbAddres("LTC-复盘及时率"));
                resultDataMap.put("ratio", ratio);
                resultDataMap.put("code", 200);
                resultDataMap.put("title", "复盘与评议");
                resultDataMap.put("msg", "获取复盘及时率成功");
                return resultDataMap;

            } catch (Exception e) {
                /* 记录解析异常日志，并返回空的结果数据Map */
                log.error("获取复盘及时率异常:"+ e.getMessage());
                resultDataMap.put("code", 500);
                resultDataMap.put("title", "复盘与评议");
                resultDataMap.put("msg", e.getMessage());
                return resultDataMap;
            }
        }


    /**
     * 获取及时率
     * @return
     */
    public Map<String, Object> getJsRate(String lcbjdCode) {
        Map<String, Object> resultDataMap = new HashMap<String, Object>();
        try {
            //开发项目档案编码
            String kfxmda_code = AppContext.getSystemProperty("ltc.yfxmda_formCode");
            //ltc里程碑阶段、ltc实际完成时间、ltc计划结束时间
            String ltc_lcbjd = AppContext.getSystemProperty("ltc.ltc_lcbjd");
            String ltc_sjwcsj = AppContext.getSystemProperty("ltc.ltc_sjwcsj");
            String ltc_jhjssj = AppContext.getSystemProperty("ltc.ltc_jhjssj");
            String bnnd = AppContext.getSystemProperty("ltc.bnnd");

            //获取'开发项目档案-项目主计划'明细表的表单字段
            FormBean kfxmda_formBean = cap4FormManager.getFormByFormCode(kfxmda_code);
            List<FormFieldBean> kfxmda_formFieldBeans = kfxmda_formBean.getAllFieldBeans();
            //获取枚举对象
            CtpEnumItem ltc_lcbjd_enum = FormCap4Kit.getEnumByCode(lcbjdCode);


            // 初始化用于构建SQL查询的变量
            String xmzjh_tableName = "";
            String ltc_lcbjd_fieldName = "";
            String ltc_sjwcsj_fieldName = "";
            String ltc_jhjssj_fieldName = "";
            String bnnd_fieldName = "";

            for (FormFieldBean formFieldBean : kfxmda_formFieldBeans) {
                String fieldDesc = StrKit.str(formFieldBean.getDesc().trim());
                if (ltc_lcbjd.equals(fieldDesc)) {
                    ltc_lcbjd_fieldName = formFieldBean.getName();
                    xmzjh_tableName = formFieldBean.getOwnerTableName();
                }
                if (ltc_sjwcsj.equals(fieldDesc)) {
                    ltc_sjwcsj_fieldName = formFieldBean.getName();
                }
                if (ltc_jhjssj.equals(fieldDesc)) {
                    ltc_jhjssj_fieldName = formFieldBean.getName();
                }
                if (bnnd.equals(fieldDesc)) {
                    bnnd_fieldName = formFieldBean.getName();
                }
            }
                /*SELECT CASE WHEN total_count = 0 THEN 0.00 ELSE ROUND((early_count * 1.0 / total_count * 100), 4) END AS ratio
                FROM (SELECT COUNT(CASE WHEN time1 < time2 AND zt = 1 THEN 1 END) AS early_count,
                COUNT(CASE WHEN time1 IS NOT NULL AND time2 IS NOT NULL AND zt = 1 THEN 1 END) AS total_count FROM data_table) AS subquery;*/
            // 根据已确定的条件构建SQL查询语句
            StringBuilder selectSql = new StringBuilder();
            //查询及时率
            if (!"".equals(xmzjh_tableName) && !"".equals(ltc_lcbjd_fieldName) && !"".equals(ltc_sjwcsj_fieldName) && !"".equals(ltc_jhjssj_fieldName)) {

                selectSql.append("SELECT CASE WHEN total_count = 0 THEN 0.00 ELSE ROUND((early_count * 1.0 / total_count * 100), 2) END AS ratio");
                selectSql.append(" FROM (SELECT COUNT(CASE WHEN ");
                selectSql.append(ltc_sjwcsj_fieldName).append(" <= ").append(ltc_jhjssj_fieldName);
                selectSql.append(" AND ").append(ltc_lcbjd_fieldName).append(" = '").append(ltc_lcbjd_enum.getId());
                selectSql.append("' AND "+bnnd_fieldName+" = '").append(LocalDate.now().getYear()).append("' THEN 1 END) AS early_count, ");
                selectSql.append(" COUNT(CASE WHEN ").append(ltc_sjwcsj_fieldName).append(" IS NOT NULL AND ");
                selectSql.append(ltc_jhjssj_fieldName).append(" IS NOT NULL AND ");
                selectSql.append(ltc_lcbjd_fieldName).append(" = '").append(ltc_lcbjd_enum.getId());
                selectSql.append("' AND "+bnnd_fieldName+" = '").append(LocalDate.now().getYear()).append("' THEN 1 END) AS total_count ");
                selectSql.append("FROM ").append(xmzjh_tableName).append(") AS subquery");
            }else {
                throw new Exception("查询及时率失败，表单字段信息有误！");
            }
            //存储及时率
            String ratio = "";

            // 日志记录构建的SQL查询语句
            log.info("查询及时率sql:" + selectSql.toString());

            //从数据库获取及时率
            List<Map<String, Object>> xszhlList = DBKit.getDataBae(selectSql.toString());
            if (!xszhlList.isEmpty()) {
                ratio = StrKit.str(xszhlList.get(0).get("ratio"));
            }

            //将数据放入结果数据Map
            resultDataMap.put("ratio", ratio);
            resultDataMap.put("code", 200);
            resultDataMap.put("msg", lcbjdCode+"获取数据成功");
            return resultDataMap;

        } catch (Exception e) {
            /* 记录解析异常日志，并返回空的结果数据Map */
            log.error(lcbjdCode+"获取数据异常:"+ e.getMessage());
            resultDataMap.put("code", 500);
            resultDataMap.put("msg", e.getMessage());
            return resultDataMap;
        }

    }

    /**
     * 获取合同决策意见闭环率
     * @return
     */
    public Map<String, Object> getHtjcyjbhl() {
        Map<String, Object> resultDataMap = new HashMap<String, Object>();
        try {
            //交付项目风险/假设及应对措施编
            String jfxmfxjsjydc_code = AppContext.getSystemProperty("ltc.jfxmfxjsjydc_code");
            //是否闭环
            String jfxmfxjsjydcs_sfbh = AppContext.getSystemProperty("ltc.jfxmfxjsjydcs_sfbh");
            //是否-是
            String sf_s = AppContext.getSystemProperty("ltc.sf_s");

            //获取'交付项目风险/假设及应对措施编'的表单字段
            FormBean jfxmfxjsjydc_formBean = cap4FormManager.getFormByFormCode(jfxmfxjsjydc_code);
            List<FormFieldBean> jfxmfxjsjydc_formFieldBeans = jfxmfxjsjydc_formBean.getAllFieldBeans();
            //获取枚举对象
            CtpEnumItem sf_s_enum = FormCap4Kit.getEnumByCode(sf_s);


            // 初始化用于构建SQL查询的变量
            String jfxmfxjsjydc_tableName = "";
            String jfxmfxjsjydcs_sfbh_fieldName = "";

            for (FormFieldBean formFieldBean : jfxmfxjsjydc_formFieldBeans) {
                String fieldDesc = StrKit.str(formFieldBean.getDesc().trim());
                if (jfxmfxjsjydcs_sfbh.equals(fieldDesc)) {
                    jfxmfxjsjydcs_sfbh_fieldName = formFieldBean.getName();
                    jfxmfxjsjydc_tableName = formFieldBean.getOwnerTableName();
                }

            }
                /*SELECT CASE WHEN total_count = 0 THEN 0.00 ELSE ROUND((sfs_count * 1.0 / total_count * 100), 2) END AS ratio FROM ( SELECT COUNT(CASE WHEN sfbh = 1 THEN 1 END) AS sfs_count, COUNT(1) AS total_count FROM data_table*/
            // 根据已确定的条件构建SQL查询语句
            StringBuilder selectSql = new StringBuilder();
            //查询闭环率
            if (!"".equals(jfxmfxjsjydc_tableName) && !"".equals(jfxmfxjsjydcs_sfbh_fieldName) ) {
                selectSql.append("SELECT CASE WHEN total_count = 0 THEN 0.00 ELSE ROUND((sfs_count * 1.0 / total_count * 100), 2) END AS ratio");
                selectSql.append(" FROM ( SELECT COUNT(CASE WHEN ").append(jfxmfxjsjydcs_sfbh_fieldName).append(" = '").append(sf_s_enum.getId()).append("' THEN 1 END) AS sfs_count, ");
                selectSql.append(" COUNT(1) AS total_count FROM ").append(jfxmfxjsjydc_tableName).append(") AS subquery");
            }else {
                throw new Exception("查询合同决策意闭环率失败，表单字段信息有误！");
            }
            //存储闭环率
            String ratio = "";

            // 日志记录构建的SQL查询语句
            log.info("查询闭环率sql:" + selectSql.toString());

            //从数据库获取闭环率
            List<Map<String, Object>> resultList = DBKit.getDataBae(selectSql.toString());
            if (!resultList.isEmpty()) {
                ratio = StrKit.str(resultList.get(0).get("ratio"));
            }

            //将数据放入结果数据Map
            resultDataMap.put("ratio", ratio);
            resultDataMap.put("code", 200);
            resultDataMap.put("msg", "闭环率获取数据成功");
            return resultDataMap;

        } catch (Exception e) {
            /* 记录解析异常日志，并返回空的结果数据Map */
            log.error("闭环率获取数据异常:"+ e.getMessage());
            resultDataMap.put("code", 500);
            resultDataMap.put("msg", e.getMessage().toString());
            return resultDataMap;
        }
    }

    /**
     * 项目及时率
     * @return
     */
    public Map<String, Object> getXmjsRate(String jssj, String kssj,int jgsj) {
        Map<String, Object> resultDataMap = new HashMap<String, Object>();
        try {
            //项目档案编码
            String xmda_code = AppContext.getSystemProperty("ltc.xmda_code");
            String bnnd = AppContext.getSystemProperty("ltc.bnnd");

            //获取项目档案的表单字段
            FormBean xmda_formBean = cap4FormManager.getFormByFormCode(xmda_code);
            List<FormFieldBean> xmda_formFieldBeans = xmda_formBean.getMasterTableBean().getFields();

            // 初始化用于构建SQL查询的变量
            String xmda_tableName = "";
            String kssj_fieldName = "";
            String jssj_fieldName = "";
            String bnnd_fieldName = "";
            for (FormFieldBean formFieldBean : xmda_formFieldBeans) {
                String fieldDesc = StrKit.str(formFieldBean.getDesc().trim());
                if (kssj.equals(fieldDesc)) {
                    kssj_fieldName = formFieldBean.getName();
                    xmda_tableName = formFieldBean.getOwnerTableName();
                }
                if (jssj.equals(fieldDesc)) {
                    jssj_fieldName = formFieldBean.getName();
                }
                if (bnnd.equals(fieldDesc)) {
                    bnnd_fieldName = formFieldBean.getName();
                }
            }
                /*-- 计算满足条件的记录数量
                WITH LateRecords AS (
                    SELECT COUNT(*) AS LateCount
                    FROM xmda
                    WHERE DATEDIFF(hour, kssj, jssj) <= 24 // WHERE TIMESTAMPDIFF(HOUR, kssj, jssj) <= 24
                ),
                -- 计算总的记录数量
                TotalRecords AS (
                    SELECT COUNT(*) AS TotalCount
                    FROM xmda
                )
                -- 计算比例
                SELECT
                    CASE
                        WHEN TR.TotalCount = 0 THEN 0.00
                        ELSE ROUND((LR.LateCount * 1.0 / TR.TotalCount*100),2)
                    END AS Ratio
                FROM LateRecords LR, TotalRecords TR;*/
            // 根据已确定的条件构建SQL查询语句
            StringBuilder selectSql = new StringBuilder();
            //查询及时率
            if (!"".equals(xmda_tableName) && !"".equals(kssj_fieldName) && !"".equals(jssj_fieldName) ) {

                // 构建子查询
                selectSql.append("SELECT ");
                selectSql.append("CASE WHEN t1.TotalCount = 0 THEN 0.00 ELSE CAST(ROUND((t2.LateCount * 1.0 / t1.TotalCount * 100), 2) AS DECIMAL(10, 2)) END AS ratio ");
                selectSql.append("FROM ");
                selectSql.append("(SELECT COUNT(*) AS TotalCount FROM ").append(xmda_tableName).append(" where ").append(kssj_fieldName).append(" is not null and ").append(jssj_fieldName).append(" is not null and ").append(bnnd_fieldName).append(" = '").append(LocalDate.now().getYear()).append("') t1,");
                selectSql.append("(SELECT COUNT(*) AS LateCount FROM ").append(xmda_tableName).append(" WHERE DATEDIFF(hour, ").append(kssj_fieldName).append(", ").append(jssj_fieldName).append(") >= ").append(jgsj).append(" and ").append(bnnd_fieldName).append(" = '").append(LocalDate.now().getYear()).append("') t2");


            }else {
                throw new Exception("查询项目及时率失败，表单字段信息有误！");
            }
            //存储项目及时率
            String ratio = "";

            // 日志记录构建的SQL查询语句
            log.info("查询项目及时率sql:" + selectSql.toString());

            //从数据库获取及时率
            List<Map<String, Object>> xszhlList = DBKit.getDataBae(selectSql.toString());
            if (!xszhlList.isEmpty()) {
                ratio = StrKit.str(xszhlList.get(0).get("ratio"));
            }

            //将数据放入结果数据Map
            resultDataMap.put("ratio", ratio);
            resultDataMap.put("code", 200);
            resultDataMap.put("msg", "获取项目及时率成功");
            return resultDataMap;

        } catch (Exception e) {
            /* 记录解析异常日志，并返回空的结果数据Map */
            log.error("获取项目及时率异常:"+ e.getMessage());
            resultDataMap.put("code", 500);
            resultDataMap.put("msg", e.getMessage());
            return resultDataMap;
        }

    }

    /**
     * 获取金额
     * @param tableCode 表单编码
     * @param statusNotes 状态字段备注
     * @param statusList 符合要求状态值
     * @param amoumtNotes 金额字段备注
     * @param amoumtNotes 金额字段备注
     * @return otherDemand 其它需求
     */
    public Double getAmount (String tableCode,String statusNotes,List<String> statusList,String amoumtNotes,String otherDemand,String statusNotes2,List<String> statusList2) {

        try {

            //获取档案的表单字段
            FormBean formBean = cap4FormManager.getFormByFormCode(tableCode);
            List<FormFieldBean> formFieldBeans = formBean.getMasterTableBean().getFields();

            // 初始化用于构建SQL查询的变量
            String tableName = "";
            String status_fieldName = "";
            String amoumt_fieldName = "";
            String demand = "";
            String statusNotes2FieldName = "";
            for (FormFieldBean formFieldBean : formFieldBeans) {
                String fieldDesc = StrKit.str(formFieldBean.getDesc().trim());
                if (statusNotes.equals(fieldDesc)) {
                    status_fieldName = formFieldBean.getName();
                    tableName = formFieldBean.getOwnerTableName();
                }
                if (amoumtNotes.equals(fieldDesc)) {
                    amoumt_fieldName = formFieldBean.getName();
                }
                if(otherDemand!=null){
                    if (otherDemand.equals(fieldDesc)) {
                        demand = formFieldBean.getName();
                    }
                }
                if(statusNotes2!=null){
                    if (statusNotes2.equals(fieldDesc)) {
                        statusNotes2FieldName = formFieldBean.getName();
                    }
                }
            }

            /*SELECT SUM(amoumt_fieldName) AS amount
            FROM tableName
            WHERE status = 1;*/
            // 根据已确定的条件构建SQL查询语句
            StringBuilder selectSql = new StringBuilder();
            //查询金额
            if (!"".equals(tableName) && !"".equals(status_fieldName) && !"".equals(amoumt_fieldName) ) {
                if (tableCode.equals("XSXZDA_001")){
                    /*SELECT amoumt_fieldName as amount from tablename where status_fieldName='statusList.get(0)'*/
                    selectSql.append("SELECT ROUND(sum(").append(amoumt_fieldName).append("), 1) as amount from ").append(tableName);
                    selectSql.append(" WHERE ").append(status_fieldName).append("='").append(statusList.get(0)).append("'");
                }else {
                    selectSql.append("SELECT ROUND(SUM(").append(amoumt_fieldName).append("),1) AS amount FROM ").append(tableName);
                    selectSql.append(" WHERE ").append(status_fieldName).append(" IN (");
                    for (String status : statusList) {
                        selectSql.append("'").append(FormCap4Kit.getEnumByCode(status).getId()).append("',");
                    }
                    selectSql.deleteCharAt(selectSql.length() - 1);
                    selectSql.append(")");
                    /*and  field0021='2024' */
                    if (otherDemand!=null){
                        selectSql.append(" and ").append(demand).append(" = '").append(String.valueOf( LocalDate.now().getYear())).append(" '");
                    }
                    if (statusNotes2!=null){
                        if (tableCode.equals("XMDA_001")){
                            selectSql.append(" and (").append(statusNotes2FieldName).append(" not IN (");
                        } else if (tableCode.equals("JHDA_001")) {
                            selectSql.append(" and ").append(statusNotes2FieldName).append(" IN (");
                        }
                        for (String status : statusList2) {
                            selectSql.append("'").append(FormCap4Kit.getEnumByCode(status).getId()).append("',");
                        }
                        if (tableCode.equals("XMDA_001")){
                            selectSql.deleteCharAt(selectSql.length() - 1);
                            selectSql.append(") or ").append(statusNotes2FieldName).append(" is null ) ");
                        } else if (tableCode.equals("JHDA_001")) {
                            selectSql.deleteCharAt(selectSql.length() - 1);
                            selectSql.append(") ");
                        }

                    }
                }


            } else {
                throw new Exception("查询金额失败，表单字段信息有误！");
            }
            //存储金额
            Double amount = 0.0;

            // 日志记录构建的SQL查询语句
            log.info("查询"+tableCode+"金额sql:" + selectSql.toString());

            //从数据库获取金额
            List<Map<String, Object>> list = DBKit.getDataBae(selectSql.toString());
            if (!list.isEmpty()) {
                String amountStr = StrKit.str(list.get(0).get("amount")).equals("")?"0":StrKit.str(list.get(0).get("amount"));
                amount =Double.parseDouble(amountStr);
            }
            return amount;

        } catch (Exception e) {
            /* 记录解析异常日志，并返回空的结果数据Map */
            log.error("获取"+tableCode+"金额异常:"+ e.getMessage());
            return null;
        }
    }
}
